const sqlite3 = require('sqlite3').verbose();
const path = require('path');

const dbPath = path.resolve(__dirname, '../../database.sqlite');

class Database {
  constructor() {
    this.db = new sqlite3.Database(dbPath, (err) => {
      if (err) {
        console.error('Error opening database:', err);
      } else {
        console.log('Connected to SQLite database');
        this.checkDatabaseState()
          .then(() => console.log('Database initialization checked'))
          .catch(err => console.error('Database initialization check failed:', err));
      }
    });
  }

  initDatabase() {
    const schema = require('fs').readFileSync(
      path.resolve(__dirname, 'schema.sql'),
      'utf8'
    );
    
    this.db.exec(schema, (err) => {
      if (err) {
        console.error('Error initializing database:', err);
      } else {
        console.log('Database initialized');
      }
    });
  }

  // CRUD 操作方法
  async createApi(name, description) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'INSERT INTO custom_apis (name, description) VALUES (?, ?)',
        [name, description],
        function(err) {
          if (err) reject(err);
          else resolve(this.lastID);
        }
      );
    });
  }

  async createParameter(apiId, paramType, paramName, paramSchema) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'INSERT INTO api_parameters (api_id, param_type, param_name, param_schema) VALUES (?, ?, ?, ?)',
        [apiId, paramType, paramName, paramSchema],
        function(err) {
          if (err) reject(err);
          else resolve(this.lastID);
        }
      );
    });
  }

  async getAllApis() {
    return new Promise((resolve, reject) => {
      this.db.all('SELECT * FROM custom_apis', [], (err, rows) => {
        if (err) reject(err);
        else resolve(rows);
      });
    });
  }

  async getApiParameters(apiId) {
    return new Promise((resolve, reject) => {
      console.log('Fetching parameters for API ID:', apiId);
      
      if (!apiId || isNaN(parseInt(apiId))) {
        console.error('Invalid API ID:', apiId);
        reject(new Error('Invalid API ID'));
        return;
      }

      this.db.all(
        'SELECT * FROM api_parameters WHERE api_id = ?',
        [apiId],
        (err, rows) => {
          if (err) {
            console.error('Error fetching parameters:', err);
            reject(err);
            return;
          }

          console.log('Found parameters:', rows);
          resolve(rows || []);
        }
      );
    });
  }

  async updateApi(id, name, description) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'UPDATE custom_apis SET name = ?, description = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
        [name, description, id],
        (err) => {
          if (err) reject(err);
          else resolve();
        }
      );
    });
  }

  async deleteApi(id) {
    return new Promise((resolve, reject) => {
      this.db.run('BEGIN TRANSACTION', (err) => {
        if (err) {
          reject(err);
          return;
        }

        this.deleteApiParameters(id)
          .then(() => {
            this.db.run('DELETE FROM custom_apis WHERE id = ?', [id], (err) => {
              if (err) {
                this.db.run('ROLLBACK');
                reject(err);
              } else {
                this.db.run('COMMIT');
                resolve();
              }
            });
          })
          .catch((err) => {
            this.db.run('ROLLBACK');
            reject(err);
          });
      });
    });
  }

  async updateParameter(apiId, paramType, paramName, paramSchema) {
    return new Promise((resolve, reject) => {
      this.db.run(
        `UPDATE api_parameters 
         SET param_schema = ?, updated_at = CURRENT_TIMESTAMP 
         WHERE api_id = ? AND param_type = ?`,
        [paramSchema, apiId, paramType],
        (err, result) => {
          if (err) {
            reject(err);
          } else {
            this.db.get(
              'SELECT changes() as changes',
              [],
              (err, row) => {
                if (err) {
                  reject(err);
                } else if (row.changes === 0) {
                  this.db.run(
                    `INSERT INTO api_parameters (api_id, param_type, param_name, param_schema) 
                     VALUES (?, ?, ?, ?)`,
                    [apiId, paramType, paramName, paramSchema],
                    (err) => {
                      if (err) reject(err);
                      else resolve();
                    }
                  );
                } else {
                  resolve();
                }
              }
            );
          }
        }
      );
    });
  }

  async getApiById(id) {
    return new Promise((resolve, reject) => {
      console.log('Fetching API with ID:', id);
      
      if (!id || isNaN(parseInt(id))) {
        console.error('Invalid API ID:', id);
        reject(new Error('Invalid API ID'));
        return;
      }

      if (!this.db) {
        console.error('Database connection not established');
        reject(new Error('Database connection not established'));
        return;
      }

      this.db.get(
        `SELECT name FROM sqlite_master WHERE type='table' AND name='custom_apis'`,
        [],
        (err, table) => {
          if (err) {
            console.error('Error checking table existence:', err);
            reject(err);
            return;
          }

          if (!table) {
            console.error('Table custom_apis does not exist');
            reject(new Error('Table custom_apis does not exist'));
            return;
          }

          this.db.get(
            'SELECT * FROM custom_apis WHERE id = ?',
            [id],
            (err, row) => {
              if (err) {
                console.error('Error fetching API:', err);
                reject(err);
                return;
              }

              console.log('Found API:', row);
              
              if (!row) {
                console.log('No API found with ID:', id);
                resolve(null);
                return;
              }

              resolve(row);
            }
          );
        }
      );
    });
  }

  async deleteApiParameters(apiId) {
    return new Promise((resolve, reject) => {
      this.db.run(
        'DELETE FROM api_parameters WHERE api_id = ?',
        [apiId],
        (err) => {
          if (err) reject(err);
          else resolve();
        }
      );
    });
  }

  async checkDatabaseState() {
    return new Promise((resolve, reject) => {
      this.db.get(
        `SELECT name FROM sqlite_master WHERE type='table' AND name='custom_apis'`,
        [],
        (err, result) => {
          if (err) {
            console.error('Error checking database state:', err);
            reject(err);
            return;
          }
          
          if (!result) {
            console.log('Tables not found, initializing database...');
            this.initDatabase();
          }
          
          resolve(true);
        }
      );
    });
  }
}

module.exports = new Database(); 